package cn.com.dhcc.ddi.datasource.dao;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import cn.com.dhcc.app.core.dao.BaseDao;
import cn.com.dhcc.app.core.dao.SimpleDaoForOracle;
import cn.com.dhcc.app.core.exception.DaoException;
import cn.com.dhcc.app.core.exception.NiceException;
import cn.com.dhcc.app.core.exception.ServiceException;
import cn.com.dhcc.ddi.datasource.vo.DataSourceVo;
import cn.com.dhcc.ddi.datasource.vo.TableColVo;
import cn.com.dhcc.ddi.datasource.vo.TableInfoVo;
import cn.com.dhcc.ddi.datasource.vo.wapper.TableColRowWapper;
import cn.com.dhcc.ddi.datasource.vo.wapper.TableInfoRowWapper;
/**
 * 
 * DataSourceOracleDao
 * @Author: fxx
 * @date: 2017-5-16
 *
 */
@Repository
public class DataSourceOracleDao extends BaseDao {
	private SimpleDaoForOracle dao;
	@Autowired
	public DataSourceDao datasourceDao;
	/**
	 * 现根据界面上的连接信息初始化数据源
	 * 
	 * @param vo
	 * @throws NiceException
	 * @throws ServiceException 
	 * @throws DaoException 
	 */
	public void initDao(DataSourceVo vo) throws DaoException {
		try {
			dao = datasourceDao.initDao(vo);
		} catch (DaoException e) {
			throw new DaoException("初始化数据源错误", e);
		}
	}
	
	/**
	 * 
	 * @MethodName: [getUserTabColumnsForOracle]
	 * @Description:[获取oracle数据库元数据]
	 * @Author:     [fxx]
	 * @CreateDate: [2017-5-16]
	 * @Params:     [ParamType ParamValue,ParamType ParamValue...]
	 * @See         [Related classes and methods]
	 * @Since       [Product template version]  
	 * @Return:     [List<TableColVo>]
	 * @Throws:     [Exception]
	 *
	 */
	public List<TableColVo> getUserTabColumnsForOracle(String schema, String tableName) throws DaoException {
		String msg = "";
		String sql ="select t1.column_name col_name," +
				"       t2.comments col_comment," +
				"       t1.data_type col_type," +
				"       t1.data_default col_default," +
				"       case" +
				"         when t4.constraint_type in ('P', 'U') then" +
				"          '1'" +
				"         else" +
				"          '0'" +
				"       end is_pk" +
				"  from all_tab_cols t1" +
				"  left join all_col_comments t2" +
				"    on t1.table_name = t2.table_name" +
				"   and t1.column_name = t2.column_name" +
				"   and t1.owner = t2.owner" +
				"  left join (select col.COLUMN_NAME," +
				"                    col.TABLE_NAME," +
				"                    con.constraint_name," +
				"                    con.constraint_type," +
				"                    con.owner" +
				"               from all_constraints con, all_cons_columns col" +
				"              where con.constraint_name = col.constraint_name" +
				"                and con.constraint_type in ('P', 'U')" +
				"                and con.owner = col.owner) t4" +
				"    on t1.table_name = t4.table_name" +
				"   and t1.COLUMN_NAME = t4.column_name" +
				"   and t1.owner = t4.owner" +
				" where t1.owner = ?" +
				"   and t1.table_name = ?" +
				" order by is_pk desc,col_name";

		try {
			return dao.list("获取表元数据", sql, new TableColRowWapper(),
					new Object[] {schema, tableName });
		} catch (DaoException e) {
			msg = "获取表元数据失败";
			throw new DaoException(msg, e);
		}
	}
	
	/**
	 * 利用获取所有被授权的表，抽取数据使用，只需查询SELECT权限
	 * 
	 * @return
	 * @throws DaoException
	 */
	public List<TableInfoVo> getToGrantTables(String owner) throws DaoException {
		String msg = "";
		String sql =  "SELECT t1.table_name, t2.comments table_comment, t1.OWNER schema_name "
					+ "  FROM (select * from user_tab_privs order by table_name) t1 "
					+ "  LEFT JOIN all_tab_comments t2 "
					+ "	   ON t1.table_name = t2.table_name "
					+ " WHERE t1.privilege='SELECT' " 
					+ "   AND t2.owner = t1.owner " 
					+ "   AND t1.owner <> ? " 
					+ " UNION "
					+ "SELECT distinct t2.table_name, t3.comments comments, t2.OWNER schema_name"
					+ "  FROM user_role_privs t1"
					+ "  LEFT join ROLE_TAB_PRIVS t2"
					+ "    ON t1.GRANTED_ROLE = t2.ROLE"
					+ "  LEFT join all_tab_comments t3"
					+ "    ON t2.table_name = t3.table_name"
					+ " WHERE t2.privilege = 'SELECT'"
					+ "	  AND t2.OWNER <> ?"
					+ "	  AND t2.OWNER <> 'SYS'"
					+ "   AND t2.ROLE <> 'DBA'"
					+ "   AND t2.owner = t3.owner";
		try {
			return dao.list("获取所有被授权的表", sql, new TableInfoRowWapper(),
					new Object[] { owner,owner });
		} catch (DaoException e) {
			msg = "获取所有表失败";
			throw new DaoException(msg, e);
		}
	}
	
	/**
	 * 
	 * @MethodName: [getUserTables]
	 * @Description:[获取所有表和视图]
	 * @Author:     [fxx]
	 * @CreateDate: [2017-5-16]
	 * @Params:     [ParamType ParamValue,ParamType ParamValue...]
	 * @See         [Related classes and methods]
	 * @Since       [Product template version]  
	 * @Return:     [List<TableInfoVo>]
	 * @Throws:     [Exception]
	 *
	 */
	public List<TableInfoVo> getUserTables(String owner) throws DaoException {
		String msg = "";
		String sql = " select t.table_name, t.comments table_comment, ?  schema_name from user_tab_comments t order by t.table_name ";

		try {
			return dao.list("获取所有表和视图", sql, new TableInfoRowWapper(),
					new Object[] { owner });
		} catch (DaoException e) {
			msg = "获取所有表失败";
			throw new DaoException(msg, e);
		}
	}
	
}
